---
id: diff
slug: /versioned/diff
title: Automatic Migration Planning
---

import Tabs from '@theme/Tabs';
import TabItem from '@theme/TabItem';

The `atlas migrate diff` command streamlines the process of writing schema migrations by automatically generating the
SQL scripts needed to migrate a database schema from its current state to the new desired state. How does it work?

Developers define the desired state and Atlas maintains the `migrations` directory, which contains the explicit SQL
scripts to move from one version to the next. The desired state can be defined using an [HCL](/atlas-schema/hcl) or
[SQL schema](/atlas-schema/sql) definition, a database [URL](../concepts/url), or an external schemas like [ORM](/atlas-schema/external).

### Summary

To get started with versioned migrations, run `atlas migrate diff`. This command generates a new migration file that aligns
the migration directory with the state defined by the desired schema. Below are a few examples of how to use this command
to generate migrations from various sources:

<Tabs>
<TabItem label="SQL Schema" value="sql">

**MySQL/MariaDB:**

```shell {3}
atlas migrate diff create_users \
  --dir "file://migrations" \
  --to "file://schema.sql" \
  --dev-url "docker://mysql/8/dev" # Or: "docker://mariadb/latest/dev"
```

**PostgreSQL:**

```shell {3}
atlas migrate diff create_users \
  --dir "file://migrations" \
  --to "file://schema.sql" \
  --dev-url "docker://postgres/15/dev?search_path=public"
```
</TabItem>
<TabItem label="HCL Schema" value="hcl">

**MySQL/MariaDB:**

```shell {3}
atlas migrate diff create_users \
  --dir "file://migrations" \
  --to "file://schema.hcl" \
  --dev-url "docker://mysql/8/dev" # Or: "docker://mariadb/latest/dev"
```

**PostgreSQL:**

```shell {3}
atlas migrate diff create_users \
  --dir "file://migrations" \
  --to "file://schema.hcl" \
  --dev-url "docker://postgres/15/dev?search_path=public"
```
</TabItem>
<TabItem label="ORM" value="orm">

**MySQL/MariaDB:**

```shell {2}
atlas migrate diff create_users \
  --env "gorm"
  --dev-url "docker://mysql/8/dev" # Or: "docker://mariadb/latest/dev"
```

**PostgreSQL:**

```shell {2}
atlas migrate diff create_users \
  --env "gorm"
  --dev-url "docker://postgres/15/dev?search_path=public"
```

To learn more on how to configure Atlas to read the desired state of a schema from an ORM definition, see the
[external schema documentation](/atlas-schema/external).

</TabItem>
<TabItem label="Database Schema" value="database-schema">

**MySQL/MariaDB:**

```shell {3}
atlas migrate diff create_users \
  --dir "file://migrations" \
  --to "mysql://root:pass@:3306/public" \
  --dev-url "docker://mysql/8/dev" # Or: "docker://mariadb/latest/dev"
```

**PostgreSQL:**

```shell {3}
atlas migrate diff create_users \
  --dir "file://migrations" \
  --to "postgres://postgres:pass@localhost:5432/database?search_path=public&sslmode=disable" \
  --dev-url "docker://postgres/15/dev?search_path=public"
```
</TabItem>
<TabItem label="Database (multiple schemas)" value="database">

**MySQL/MariaDB:**

```shell {3}
atlas migrate diff create_users \
  --dir "file://migrations" \
  --to "mysql://root:pass@:3306/" \
  --dev-url "docker://mysql/8" # Or: "docker://mariadb"
```

**PostgreSQL:**

```shell {3}
atlas migrate diff create_users \
  --dir "file://migrations" \
  --to "postgres://postgres:pass@localhost:5432/database?sslmode=disable" \
  --dev-url "docker://postgres/15/dev"
```
</TabItem>
</Tabs>

:::info [Ensure linear migration history](/lint/analyzers#non-linear-changes)

Teams that have connected their project to Atlas Cloud (see [setup](/cloud/setup-ci)) will get a prompt in the CLI if
their migration directory is out of sync with the latest version in Atlas Cloud. This ensures that new migration files are
added in a sequential order, preventing unexpected behavior. For example:

```text
atlas migrate diff --env dev

//highlight-start
? Your directory is outdated (2 migrations behind). Continue or Abort:
//underline-next-line
▸ Continue (Rebase later)
  Abort (Pull changes and re-run the command)
//highlight-end
```

Additionally, the [`atlas migrate lint`](/versioned/lint) command helps enforce this requirement during the CI stage.
Learn more on how to integrate Atlas into your [**GitHub Actions**](/cloud/setup-ci) or [**GitLab**](/guides/ci-platforms/gitlab) CI pipelines.
:::

### Generate migrations from HCL schemas

Suppose we have an Atlas schema with one table and an empty migration directory:

<Tabs defaultValue="mysql">
<TabItem value="mysql" label="MySQL">

```hcl title="schema.hcl"
schema "test" {}

table "users" {
  schema = schema.test
  column "id" {
    type = int
  }
}
```

</TabItem>
<TabItem value="maria" label="MariaDB">

```hcl title="schema.hcl"
schema "test" {}

table "users" {
  schema = schema.test
  column "id" {
    type = int
  }
}
```

</TabItem>
<TabItem value="postgres" label="PostgreSQL">

```hcl title="schema.hcl"
schema "test" {}

table "users" {
  schema = schema.test
  column "id" {
    type = int
  }
}
```

</TabItem>
<TabItem value="sqlite" label="SQLite">

```hcl title="schema.hcl"
schema "default" {}

table "users" {
  schema = schema.default
  engine = Memory
  column "id" {
    type = Int
  }
}
```

</TabItem>
<TabItem value="sqlserver" label="SQL Server">

```hcl title="schema.hcl"
schema "dbo" {}

table "users" {
  schema = schema.dbo
  column "id" {
    type = int
  }
}
```

</TabItem>
<TabItem value="clickhouse" label="ClickHouse">

```hcl title="schema.hcl"
schema "default" {}

table "users" {
  schema = schema.default
  engine = Memory
  column "id" {
    type = Int
  }
}
```

</TabItem>
</Tabs>

Let's run `atlas migrate diff` with the necessary parameters to generate a migration script for
creating our `users` table:

* `--dir` the URL to the migration directory, by default it is `file://migrations`.
* `--to` the URL of the desired state, an [HCL](/atlas-schema/hcl) or SQL schema definition, or a database [URL](../concepts/url).
* `--dev-url` a [URL](/concepts/url) to a [Dev Database](/concepts/dev-database) that will be used to compute the diff.
* `--format` (optional) - [Go template](https://pkg.go.dev/text/template) to use to format the output.

<Tabs defaultValue="mysql">
<TabItem value="mysql" label="MySQL">

```shell
atlas migrate diff create_users \
  --dir "file://migrations" \
  --to "file://schema.hcl" \
  --dev-url "docker://mysql/8/test"
```

</TabItem>
<TabItem value="maria" label="MariaDB">

```shell
atlas migrate diff create_users \
  --dir "file://migrations" \
  --to "file://schema.hcl" \
  --dev-url "docker://mariadb/latest/test"
```

</TabItem>
<TabItem value="postgres" label="PostgreSQL">

```shell
atlas migrate diff create_users \
  --dir "file://migrations" \
  --to "file://schema.hcl" \
  --dev-url "docker://postgres/15/test?search_path=public"
```

</TabItem>
<TabItem value="sqlite" label="SQLite">

```shell
atlas migrate diff create_users \
  --dir "file://migrations" \
  --to "file://schema.hcl" \
  --dev-url "sqlite://file?mode=memory"
```

</TabItem>
<TabItem value="sqlserver" label="SQL Server">

```shell
atlas migrate diff create_users \
  --dir "file://migrations" \
  --to "file://schema.hcl" \
  --dev-url "docker://sqlserver/2022-latest/test"
```

</TabItem>
<TabItem value="clickhouse" label="ClickHouse">

```shell
atlas migrate diff create_users \
  --dir "file://migrations" \
  --to "file://schema.hcl" \
  --dev-url "docker://clickhouse/23.11/test"
```

</TabItem>
</Tabs>

:::info
If you are working with multiple schemas and want qualified identifiers to appear in the diff output, omit the schema
name in the `--dev-url` flag. In PostgreSQL, this corresponds to the `search_path`; in MySQL/MariaDB, it is the database
in the URL path.
:::

Run `ls migrations`, and you will notice Atlas created 2 files:

<Tabs
defaultValue="migration_file"
values={[
{label: '20220811074144_create_users.sql', value: 'migration_file'},
{label: 'atlas.sum', value: 'sum_file'},
]}>
<TabItem value="migration_file">

By default, migration files are named with the following format `{{ now }}_{{ name }}.sql`.
If you wish to use a different file format, use the `format` query parameter in the directory URL.

```sql
-- create "users" table
CREATE TABLE `users` (`id` int NOT NULL) CHARSET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
```

</TabItem>
<TabItem value="sum_file">

In addition to the migration directory, Atlas maintains a file name `atlas.sum` which is used
to ensure the integrity of the migration directory and force developers to deal with situations
where migration order or contents was modified after the fact.

```text
h1:t1fEP1rSsGf1gYrYCjsGyEyuM0cnhATlq93B7h8uXxY=
20220811074144_create_users.sql h1:liZcCBbAn/HyBTqBAEVar9fJNKPTb2Eq+rEKZeCFC9M=
```

</TabItem>
</Tabs>

Let's repeat the process above by changing our HCL schema file and running Atlas migration authoring again.
We add a new column `name` to our HCL schema:
<Tabs defaultValue="mysql">
<TabItem value="mysql" label="MySQL">

```hcl title="schema.hcl" {8-10}
schema "test" {}

table "users" {
  schema = schema.test
  column "id" {
    type = int
  }
  column "name" {
    type = varchar(255)
  }
}
```

</TabItem>
<TabItem value="maria" label="MariaDB">

```hcl title="schema.hcl" {8-10}
schema "test" {}

table "users" {
  schema = schema.test
  column "id" {
    type = int
  }
  column "name" {
    type = varchar(255)
  }
}
```

</TabItem>
<TabItem value="sqlite" label="SQLite">

```hcl title="schema.hcl" {8-10}
schema "test" {}

table "users" {
  schema = schema.test
  column "id" {
    type = int
  }
  column "name" {
    type = varchar(255)
  }
}
```

</TabItem>
<TabItem value="postgres" label="PostgreSQL">

```hcl title="schema.hcl" {8-10}
schema "test" {}

table "users" {
  schema = schema.test
  column "id" {
    type = int
  }
  column "name" {
    type = varchar(255)
  }
}
```

</TabItem>
<TabItem value="sqlserver" label="SQL Server">

```hcl title="schema.hcl" {8-10}
schema "dbo" {}

table "users" {
  schema = schema.dbo
  column "id" {
    type = int
  }
  column "name" {
    type = nvarchar(255)
  }
}
```

</TabItem>
<TabItem value="clickhouse" label="ClickHouse">

```hcl title="schema.hcl" {8-10}
schema "default" {}

table "users" {
  schema = schema.test
  engine = Memory
  column "id" {
    type = Int
  }
  column "name" {
    type = String
  }
}
```

</TabItem>
</Tabs>

Then, run `atlas migrate diff`:

<Tabs defaultValue="mysql">
<TabItem value="mysql" label="MySQL">

```shell
atlas migrate diff add_users_name \
  --dir "file://migrations" \
  --to "file://schema.hcl" \
  --dev-url "docker://mysql/8/dev"
```

</TabItem>
<TabItem value="maria" label="MariaDB">

```shell
atlas migrate diff add_users_name \
  --dir "file://migrations" \
  --to "file://schema.hcl" \
  --dev-url "docker://mariadb/latest/dev"
```

</TabItem>
<TabItem value="postgres" label="PostgreSQL">

```shell
atlas migrate diff add_users_name \
  --dir "file://migrations" \
  --to "file://schema.hcl" \
  --dev-url "docker://postgres/15/dev?search_path=public"
```

</TabItem>
<TabItem value="sqlite" label="SQLite">

```shell
atlas migrate diff add_users_name \
  --dir "file://migrations" \
  --to "file://schema.hcl" \
  --dev-url "sqlite://dev?mode=memory"
```

</TabItem>
<TabItem value="sqlserver" label="SQL Server">

```shell
atlas migrate diff add_users_name \
  --dir "file://migrations" \
  --to "file://schema.hcl" \
  --dev-url "docker://sqlserver/2022-latest"
```

</TabItem>
<TabItem value="clickhouse" label="ClickHouse">

```shell
atlas migrate diff add_users_name \
  --dir "file://migrations" \
  --to "file://schema.hcl" \
  --dev-url "docker://clickhouse/23.11/dev"
```

</TabItem>
</Tabs>

:::info
If you are working with multiple schemas and want qualified identifiers to appear in the diff output, omit the schema
name in the `--dev-url` flag. In PostgreSQL, this corresponds to the `search_path`; in MySQL/MariaDB, it is the database
in the URL path.
:::

You will notice Atlas added a new file to the migration directory:

<Tabs
defaultValue="migration_file2"
values={[
{label: '20220811074144_create_users.sql', value: 'migration_file1'},
{label: '20220811074314_add_users_name.sql', value: 'migration_file2'},
{label: 'atlas.sum', value: 'sum_file'},
]}>
<TabItem value="migration_file1">

```sql
-- create "users" table
CREATE TABLE `users` (`id` int NOT NULL) CHARSET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
```

</TabItem>
<TabItem value="migration_file2">

```sql
-- modify "users" table
ALTER TABLE `users` ADD COLUMN `name` varchar(255) NOT NULL;
```

</TabItem>
<TabItem value="sum_file">

```text
h1:w2ODzVxhTKdBVBdzqntHw7rHV8lKQF98TmNevOEZfIo=
20220811074144_create_users_table.sql h1:KnMSZM/E4TBGidYCZ+UHxkHEWaRWeyuPIUjSHRybQqA=
20220811074314_add_users_name.sql h1:jUpaANgD0SjI5DjaHuJxtHZ6Wq98act0MmE5oZ+NRU0=
```

</TabItem>
</Tabs>

The following diagram explains how it works. Atlas loads the **current state**
by replaying the migration directory onto the provided [dev database](/concepts/dev-database),
compares it against the **desired state** and writes a new migration script for moving from the current
to the desired state.

![Diff From Schema](https://atlasgo.io/uploads/versioned/diff-from-schema.png)


### Generate migrations from SQL schemas

Atlas allows you to define your desired state using SQL schemas. An SQL schema can be a single file containing `CREATE`
and `ALTER` statements, or a directory with multiple SQL files. As an example, suppose we have an SQL schema with one
table and an empty migration directory:

<Tabs defaultValue="mysql">
<TabItem value="mysql" label="MySQL">

```sql title="schema.sql"
CREATE TABLE `users` (
  `id` int NOT NULL
)
```

</TabItem>
<TabItem value="maria" label="MariaDB">

```sql title="schema.sql"
CREATE TABLE `users` (
  `id` int NOT NULL
)
```

</TabItem>
<TabItem value="postgres" label="PostgreSQL">

```sql title="schema.sql"
CREATE TABLE `users` (
  `id` int NOT NULL
)
```

</TabItem>
<TabItem value="sqlite" label="SQLite">

```sql title="schema.sql"
CREATE TABLE `users` (
  `id` int NOT NULL
)
```

</TabItem>
<TabItem value="sqlserver" label="SQL Server">

```sql title="schema.sql"
CREATE TABLE [users] (
  [id] int NOT NULL
)
```

</TabItem>
<TabItem value="clickhouse" label="ClickHouse">

```sql title="schema.sql"
CREATE TABLE `users` (
  `id` int NOT NULL
) ENGINE = Memory
```

</TabItem>
</Tabs>

Let's run `atlas migrate diff` with the necessary parameters to generate a migration script for
creating our `users` table:

* `--dir` the URL to the migration directory, by default it is `file://migrations`.
* `--to` the URL of the desired state, an [HCL](/atlas-schema/hcl) or SQL schema definition, or a database [URL](../concepts/url).
* `--dev-url` a [URL](/concepts/url) to a [Dev Database](/concepts/dev-database) that will be used to compute the diff.

<Tabs defaultValue="mysql">
<TabItem value="mysql" label="MySQL">

```shell
atlas migrate diff create_users \
  --dir "file://migrations" \
  --to "file://schema.sql" \
  --dev-url "docker://mysql/8/dev"
```

</TabItem>
<TabItem value="maria" label="MariaDB">

```shell
atlas migrate diff create_users \
  --dir "file://migrations" \
  --to "file://schema.sql" \
  --dev-url "docker://mariadb/latest/dev"
```

</TabItem>
<TabItem value="postgres" label="PostgreSQL">

```shell
atlas migrate diff create_users \
  --dir "file://migrations" \
  --to "file://schema.sql" \
  --dev-url "docker://postgres/15/dev?search_path=public"
```

</TabItem>
<TabItem value="sqlite" label="SQLite">

```shell
atlas migrate diff create_users \
  --dir "file://migrations" \
  --to "file://schema.sql" \
  --dev-url "sqlite://file?mode=memory"
```

</TabItem>
<TabItem value="sqlserver" label="SQL Server">

```shell
atlas migrate diff create_users \
  --dir "file://migrations" \
  --to "file://schema.sql" \
  --dev-url "docker://sqlserver/2022-latest/dev"
```

</TabItem>
<TabItem value="clickhouse" label="ClickHouse">

```shell
atlas migrate diff create_users \
  --dir "file://migrations" \
  --to "file://schema.sql" \
  --dev-url "docker://clickhouse/23.11/dev"
```

</TabItem>
</Tabs>

:::info
If you are working with multiple schemas and want qualified identifiers to appear in the diff output, omit the schema
name in the `--dev-url` flag. In PostgreSQL, this corresponds to the `search_path`; in MySQL/MariaDB, it is the database
in the URL path.
:::

Run `ls migrations`, and you will notice Atlas created 2 files:

<Tabs
defaultValue="migration_file"
values={[
{label: '20220811074144_create_users.sql', value: 'migration_file'},
{label: 'atlas.sum', value: 'sum_file'},
]}>
<TabItem value="migration_file">

By default, migration files are named with the following format `{{ now }}_{{ name }}.sql`.
If you wish to use a different file format, use the `format` query parameter in the directory URL.

```sql
-- create "users" table
CREATE TABLE `users` (`id` int NOT NULL) CHARSET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
```

</TabItem>
<TabItem value="sum_file">

In addition to the migration directory, Atlas maintains a file name `atlas.sum` which is used
to ensure the integrity of the migration directory and force developers to deal with situations
where migration order or contents was modified after the fact.

```text
h1:t1fEP1rSsGf1gYrYCjsGyEyuM0cnhATlq93B7h8uXxY=
20220811074144_create_users.sql h1:liZcCBbAn/HyBTqBAEVar9fJNKPTb2Eq+rEKZeCFC9M=
```

</TabItem>
</Tabs>

Let's repeat the process above by changing our SQL schema file and running Atlas migration authoring again.
We add a new column `name` to our SQL schema:

<Tabs defaultValue="mysql">
<TabItem value="mysql" label="MySQL">

```sql title="schema.sql" {3}
CREATE TABLE `users` (
  `id` int NOT NULL,
  `name` varchar(255) NOT NULL
)
```

</TabItem>
<TabItem value="maria" label="MariaDB">

```sql title="schema.sql" {3}
CREATE TABLE `users` (
  `id` int NOT NULL,
  `name` varchar(255) NOT NULL
)
```

</TabItem>
<TabItem value="postgres" label="PostgreSQL">

```sql title="schema.sql" {3}
CREATE TABLE `users` (
  `id` int NOT NULL,
  `name` varchar(255) NOT NULL
)
```

</TabItem>
<TabItem value="sqlite" label="SQLite">

```sql title="schema.sql" {3}
CREATE TABLE `users` (
  `id` int NOT NULL,
  `name` varchar(255) NOT NULL
)
```

</TabItem>
<TabItem value="sqlserver" label="SQL Server">

```sql title="schema.sql" {3}
CREATE TABLE [users] (
  [id] int NOT NULL,
  [name] nvarchar(255) NOT NULL
)
```

</TabItem>
<TabItem value="clickhouse" label="ClickHouse">

```sql title="schema.sql" {3}
CREATE TABLE `users` (
  `id` int NOT NULL,
  `name` varchar(255) NOT NULL
) ENGINE = Memory
```

</TabItem>
</Tabs>

Then, run `atlas migrate diff`:


<Tabs defaultValue="mysql">
<TabItem value="mysql" label="MySQL">

```shell
atlas migrate diff add_users_name \
  --dir "file://migrations" \
  --to "file://schema.sql" \
  --dev-url "docker://mysql/8/dev"
```

</TabItem>
<TabItem value="maria" label="MariaDB">

```shell
atlas migrate diff add_users_name \
  --dir "file://migrations" \
  --to "file://schema.sql" \
  --dev-url "docker://mariadb/latest/dev"
```

</TabItem>
<TabItem value="postgres" label="PostgreSQL">

```shell
atlas migrate diff add_users_name \
  --dir "file://migrations" \
  --to "file://schema.sql" \
  --dev-url "docker://postgres/15/dev?search_path=public"
```

</TabItem>
<TabItem value="sqlite" label="SQLite">

```shell
atlas migrate diff add_users_name \
  --dir "file://migrations" \
  --to "file://schema.sql" \
  --dev-url "sqlite://file?mode=memory"
```

</TabItem>
<TabItem value="sqlserver" label="SQL Server">

```shell
atlas migrate diff add_users_name \
  --dir "file://migrations" \
  --to "file://schema.sql" \
  --dev-url "docker://sqlserver/2022-latest/dev"
```

</TabItem>
<TabItem value="clickhouse" label="ClickHouse">

```shell
atlas migrate diff add_users_name \
  --dir "file://migrations" \
  --to "file://schema.sql" \
  --dev-url "docker://clickhouse/23.11/dev"
```

</TabItem>
</Tabs>

:::info
If you are working with multiple schemas and want qualified identifiers to appear in the diff output, omit the schema
name in the `--dev-url` flag. In PostgreSQL, this corresponds to the `search_path`; in MySQL/MariaDB, it is the database
in the URL path.
:::

You will notice Atlas added a new file to the migration directory:

<Tabs
defaultValue="migration_file2"
values={[
{label: '20220811074144_create_users.sql', value: 'migration_file1'},
{label: '20220811074314_add_users_name.sql', value: 'migration_file2'},
{label: 'atlas.sum', value: 'sum_file'},
]}>
<TabItem value="migration_file1">

```sql
-- create "users" table
CREATE TABLE `users` (`id` int NOT NULL) CHARSET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
```

</TabItem>
<TabItem value="migration_file2">

```sql
-- modify "users" table
ALTER TABLE `users` ADD COLUMN `name` varchar(255) NOT NULL;
```

</TabItem>
<TabItem value="sum_file">

```text
h1:w2ODzVxhTKdBVBdzqntHw7rHV8lKQF98TmNevOEZfIo=
20220811074144_create_users_table.sql h1:KnMSZM/E4TBGidYCZ+UHxkHEWaRWeyuPIUjSHRybQqA=
20220811074314_add_users_name.sql h1:jUpaANgD0SjI5DjaHuJxtHZ6Wq98act0MmE5oZ+NRU0=
```

</TabItem>
</Tabs>

To summarize, the example above explains how Atlas loads the **current state** by replaying the migration directory onto
the specified [dev-database](/concepts/dev-database), compares it to the **desired state** defined in the SQL schema file,
and writes a new migration script for moving from the current to the desired state.

### Generate migrations from database schemas

Suppose we have a database with a `users` table that was created manually or by an ORM like [Ent](https://entgo.io),
we can tell Atlas that this is our **desired** state, and we want to generate a migration script to create this table.

```text
mysql> describe users;

+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| id    | int  | YES  |     | NULL    |       |
+-------+------+------+-----+---------+-------+
```

Let's run `atlas migrate diff` with the necessary parameters to generate a migration script for
creating our `users` table:

```shell
atlas migrate diff create_users \
  --dir "file://migrations" \
  --to "mysql://root:pass@:3306/public" \
  --dev-url "docker://mysql/8/dev"
```

Run `ls migrations`, and you will notice Atlas created 2 files:

<Tabs
defaultValue="migration_file"
values={[
{label: '20220811102532_create_users.sql', value: 'migration_file'},
{label: 'atlas.sum', value: 'sum_file'},
]}>
<TabItem value="migration_file">

By default, migration files are named with the following format `{{ now }}_{{ name }}.sql`.
If you wish to use a different file format, use the `format` query parameter in the directory URL.

```sql
-- create "users" table
CREATE TABLE `users` (`id` int NOT NULL) CHARSET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
```

</TabItem>
<TabItem value="sum_file">

In addition to the migration directory, Atlas maintains a file name `atlas.sum` which is used
to ensure the integrity of the migration directory and force developers to deal with situations
where migration order or contents was modified after the fact.

```text
h1:/6UW2WaPV1laJpEB7oVesKB9RuL59dgDhlTp5atDbNg=
20220811102532_create_users.sql h1:IJiVpbh3FLMeDJSzxxKPuFU3m4AHBgThBfs3VFXAXVo=
```

</TabItem>
</Tabs>

### Generate migrations with custom qualifiers

When working on a specific database schema, Atlas generates migration scripts without schema qualifiers
to allow executing them multiple times on different schemas. However, in some cases, it is necessary
to have those qualifiers. To address this, Atlas allows passing another flag to `migrate diff` named `--qualifier`.

Let's run the [example above](#generate-migration-from-database-schema), with the `--qualifier` flag and compare
the output:

```shell
atlas migrate diff create_users \
  --dir "file://migrations" \
  --to "file://schema.hcl" \
  --dev-url "docker://mysql/8/dev" \
  --qualifier "market"
```

Running `cat migrations/*.sql` will print the same migration script but the `users` table will be qualified with
the `market` schema:

```sql
-- create "users" table
CREATE TABLE `market`.`users` (`id` int NOT NULL, `name` varchar(255) NOT NULL) CHARSET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
```

### Generate migrations with custom formats

Some migration tools use a different file format than the one used by Atlas. You can control the format of the migration
directory by passing in the `format` query parameter to the migration directory URL.

```shell
atlas migrate diff create_users \
  --dir "file://migrations?format=golang-migrate" \
  --to "file://schema.hcl" \
  --dev-url "docker://mysql/8/dev"
```

Run `ls migrations`, and you will notice Atlas created 3 files:

<Tabs
defaultValue="migration_file_up"
values={[
{label: '20220811114629_create_users.up.sql', value: 'migration_file_up'},
{label: '20220811114629_create_users.down.sql', value: 'migration_file_down'},
{label: 'atlas.sum', value: 'sum_file'},
]}>
<TabItem value="migration_file_up">

```sql
-- create "users" table
CREATE TABLE `users` (`name` int NOT NULL) CHARSET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
```

</TabItem>
<TabItem value="migration_file_down">

```sql
-- reverse: create "users" table
DROP TABLE `users`;
```

</TabItem>
<TabItem value="sum_file">

In addition to the migration directory, Atlas maintains a file name `atlas.sum` which is used
to ensure the integrity of the migration directory and force developers to deal with situations
where migration order or contents was modified after the fact.

```text
h1:NRHsNIjvSSzprr/EzMdtszQg3t3pVLk4G4N1tX4rMfk=
20220811114629_create_users.up.sql h1:Ng3GHrdk2davokjOctgVdxC+6QsK4JzaLX6RT3QstJc=
```

</TabItem>
</Tabs>

### Generate migrations for the entire database

Atlas supports generating migrations for databases or multiple schemas. In PostgreSQL, a database can
be created with the `CREATE DATABASE` command and can hold multiple schemas. In MySQL however, a database
is an instance with one or more schemas.

Suppose we have an Atlas schema that defines two database schemas where each one contains a single table.

```hcl title="schema.hcl"
schema "auth" {}
schema "market" {}

table "users" {
  schema = schema.market
  column "name" {
    type = int
  }
}

table "tokens" {
  schema = schema.auth
  column "value" {
    type = int
  }
}
```

Let's run `atlas migrate diff` to generate migration scripts for creating the entire schema.
However, unlike the previous examples where the `--dev-url` flag was set to a [URL](concepts/url.mdx)
of a specific schema, in this case we omit the schema name from the connection string.

<Tabs
defaultValue="mysql"
values={[
{label: 'MySQL', value: 'mysql'},
{label: 'PostgreSQL', value: 'postgres'},
]}>
<TabItem value="mysql">

```shell
atlas migrate diff create_all \
  --dir "file://migrations" \
  --to "file://schema.hcl" \
  --dev-url "docker://mysql/8"
```

Running `cat migrations/*.sql` will print the followings:

```sql
-- add new schema named "auth"
CREATE DATABASE `auth`;
-- add new schema named "market"
CREATE DATABASE `market`;
-- create "tokens" table
CREATE TABLE `auth`.`tokens` (`value` int NOT NULL) CHARSET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
-- create "users" table
CREATE TABLE `market`.`users` (`name` int NOT NULL) CHARSET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
```

</TabItem>
<TabItem value="postgres">

```shell
atlas migrate diff create_all \
  --dir "file://migrations" \
  --to "file://schema.hcl" \
  --dev-url "docker://postgres/15"
```

Running `cat migrations/*.sql` will print the followings:

```sql
-- Add new schema named "auth"
CREATE SCHEMA "auth";
-- Add new schema named "market"
CREATE SCHEMA "market";
-- create "tokens" table
CREATE TABLE "auth"."tokens" ("value" integer NOT NULL);
-- create "users" table
CREATE TABLE "market"."users" ("name" integer NOT NULL);
```

</TabItem>
</Tabs>

As you can see, Atlas generates statements for creating the `auth` and `market` schemas,
and added them as qualifiers in the created tables.


## Diff Policy

Atlas allows configuring the schema diffing policy in [project configuration](../atlas-schema/projects.mdx) to fine-tune
or modify suggested changes before they are written to the migration directory:

<Tabs>
<TabItem label="Skip Destructive" value="skip">

```hcl title="atlas.hcl"
variable "destructive" {
  type    = bool
  default = false
}

env "local" {
  migration {
    dir = "file://migrations"
  }
  diff {
    skip {
      drop_schema = !var.destructive
      drop_table  = !var.destructive
    }
  }
}
```

The usage is as follows:

```go
atlas migrate diff --env "local" --var "destructive=true"
```

</TabItem>
<TabItem label="Concurrent Indexes" value="concurrent_indexes">

```hcl title="atlas.hcl"
env "local" {
  diff {
    // By default, indexes are not created or dropped concurrently.
    concurrent_index {
      add  = true
      drop = true
    }
  }
}
```

</TabItem>
</Tabs>

## Indented SQL

The `migrate diff` command generates a list of SQL statements without indentation by default. If you would like to
generate the SQL statements with indentation, use the `--format` flag. For example:

```shell {1}
# Indent SQL statements with 2 spaces.
atlas migrate diff create_users \
  --dir "file://migrations" \
  --to "file://schema.hcl" \
  --dev-url "docker://mysql/8/dev" \
  --format '{{ sql . "  " }}'
```

## Reference

[CLI Command Reference](/cli-reference#atlas-migrate-diff)